{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 大语言模型与pandas数据分析应用\n",
    "\n",
    "langchain下的pandas代理\n",
    "\n",
    "https://api.python.langchain.com/en/latest/agents/langchain_experimental.agents.agent_toolkits.pandas.base.create_pandas_dataframe_agent.html\n",
    "\n",
    "pandas-ai\n",
    "\n",
    "https://pandas-ai.com/\n",
    "\n",
    "pandas数据分析与sql数据分析比较"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.utilities import PythonREPL\n",
    "from langchain_core.utils.function_calling import convert_to_openai_tool\n",
    "from langchain_core.output_parsers import JsonOutputParser\n",
    "from dashscope import Generation\n",
    "import dashscope\n",
    "from langchain.tools.render import render_text_description\n",
    "from operator import itemgetter\n",
    "from langchain_core.tools import tool\n",
    "import pandas as pd\n",
    "import io\n",
    " \n",
    "dashscope.api_key = \"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 基于LLM的pandas数据分析代理实现\n",
    "\n",
    "pythonrepl本质上就是python自带的执行函数 exec(source, globals=None, locals=None, /)\n",
    "\n",
    "https://blog.csdn.net/huanghong6956/article/details/115454956\n",
    "\n",
    "langchain封装的执行函数\n",
    "\n",
    "https://api.python.langchain.com/en/latest/utilities/langchain_experimental.utilities.python.PythonREPL.html\n",
    "\n",
    "* globals传参\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "r1=pd.read_csv(r\"F:\\langchain\\15\\t_alibaba_data3.txt\",names=[\"user_id\",\"brand_id\",\"behavior_type\",\"date\"],sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 182880 entries, 0 to 182879\n",
      "Data columns (total 4 columns):\n",
      " #   Column         Non-Null Count   Dtype \n",
      "---  ------         --------------   ----- \n",
      " 0   user_id        182880 non-null  int64 \n",
      " 1   brand_id       182880 non-null  int64 \n",
      " 2   behavior_type  182880 non-null  int64 \n",
      " 3   date           182880 non-null  object\n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 5.6+ MB\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(df_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "buf = io.StringIO()\n",
    "r1.info(buf=buf)\n",
    "df_info = buf.getvalue()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'type': 'function',\n",
       "  'function': {'name': 'coderunner',\n",
       "   'description': 'coderunner(code: str) -> dict - python代码执行器',\n",
       "   'parameters': {'type': 'object',\n",
       "    'properties': {'code': {'type': 'string'}},\n",
       "    'required': ['code']}}}]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[convert_to_openai_tool(coderunner)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9\\n'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p=PythonREPL()\n",
    "p.run(\"a=9\\nprint(a)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "parser = JsonOutputParser()\n",
    "\n",
    "@tool\n",
    "def coderunner(code: str) -> dict:\n",
    "    \"\"\"python代码执行器\"\"\"\n",
    "    run_code=PythonREPL(_globals={\"df\":r1})\n",
    "    res=run_code.run(code)\n",
    "    if res=='':\n",
    "        return run_code\n",
    "    else:\n",
    "        return res\n",
    "\n",
    "def get_response_t(messages):\n",
    "    response = Generation.call(\n",
    "        model='qwen-max',\n",
    "        messages=messages,\n",
    "        tools=[convert_to_openai_tool(coderunner)],\n",
    "        result_format='message', # 将输出设置为message形式\n",
    "    )\n",
    "    return response\n",
    "\n",
    "def prompt_data(content):\n",
    "    system_prompt_t=f\"\"\"已知代码中数据信息如下：\n",
    "{df_info}\n",
    "此数据已经赋值给全局变量df，在已知df的基础上基于pandas编写代码然后调用工具coderunner完成任务,注意统计结果的变量名必须是result_data\n",
    "\"\"\"\n",
    "    prompt=[{\"role\":\"system\",\"content\":system_prompt_t},\\\n",
    "            {\"role\":\"user\",\"content\":\"总共有多少行记录\"},\\\n",
    "            {'role': 'assistant', 'content': '', 'tool_calls': [{'function': {'name': 'coderunner', 'arguments': '{\"code\": \"import pandas as pd\\nresult_data=len(df)\"}'}}]}]\n",
    "    prompt.append({\"role\":\"user\",\"content\":content})\n",
    "    return prompt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_y=prompt_data(\"获取每个用户的记录数，并输出前五个数据\")\n",
    "res=get_response_t(p_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "import pandas as pd\n",
      "user_record_counts = df['user_id'].value_counts()\n",
      "result_data = user_record_counts.head(5)\n"
     ]
    }
   ],
   "source": [
    "code=parser.parse(res.output.choices[0].message['tool_calls'][0]['function']['arguments'])[\"code\"]\n",
    "print(code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "run_res=coderunner.invoke(code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id\n",
       "2940000     2407\n",
       "4184250     1473\n",
       "3031750     1343\n",
       "6874250     1275\n",
       "12215000    1258\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_res.dict()['locals']['result_data']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "04/16    59\n",
       "04/17    13\n",
       "04/18    13\n",
       "04/19    35\n",
       "04/20    41\n",
       "         ..\n",
       "08/06    27\n",
       "08/08    50\n",
       "08/09    16\n",
       "08/11    24\n",
       "08/12    17\n",
       "Name: user_id, Length: 91, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p_y=prompt_data(\"获取用户2940000每天的记录数\")\n",
    "res=get_response_t(p_y)\n",
    "code=parser.parse(res.output.choices[0].message['tool_calls'][0]['function']['arguments'])[\"code\"]\n",
    "run_res=coderunner.invoke(code)\n",
    "run_res.dict()['locals']['result_data']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### classwork1\n",
    "\n",
    "1, 利用上面的函数，完成数据统计问答\n",
    "\n",
    "2，在streamlit上实现数据统计问答应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 结合LLM的pyecharts可视化\n",
    "\n",
    "pyecharts\n",
    "\n",
    "https://gallery.pyecharts.org/#/\n",
    "\n",
    "\n",
    "pyecharts图表在streamlit显示方法\n",
    "\n",
    "https://github.com/andfanilo/streamlit-echarts\n",
    "\n",
    "https://cloud.tencent.com/developer/article/2324673\n",
    "\n",
    "* 引号的问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a1=[2,3,4]\n",
    "type(a1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "04/16    59\n",
       "04/17    13\n",
       "04/18    13\n",
       "04/19    35\n",
       "04/20    41\n",
       "         ..\n",
       "08/06    27\n",
       "08/08    50\n",
       "08/09    16\n",
       "08/11    24\n",
       "08/12    17\n",
       "Name: user_id, Length: 91, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_res.dict()['locals']['result_data']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_0=run_res.dict()[\"locals\"][\"result_data\"]\n",
    "buf = io.StringIO()\n",
    "data_0.info(buf=buf)\n",
    "data_0_info = buf.getvalue()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Index: 91 entries, 04/16 to 08/12\n",
      "Series name: user_id\n",
      "Non-Null Count  Dtype\n",
      "--------------  -----\n",
      "91 non-null     int64\n",
      "dtypes: int64(1)\n",
      "memory usage: 3.5+ KB\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(data_0_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "04/16    59\n",
       "04/17    13\n",
       "04/18    13\n",
       "04/19    35\n",
       "04/20    41\n",
       "         ..\n",
       "08/06    27\n",
       "08/08    50\n",
       "08/09    16\n",
       "08/11    24\n",
       "08/12    17\n",
       "Name: user_id, Length: 91, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "@tool\n",
    "def visrunner(code: str) -> dict:\n",
    "    \"\"\"python代码执行器\"\"\"\n",
    "    run_code=PythonREPL(_globals={\"res_data\":data_0})\n",
    "    res=run_code.run(code)\n",
    "    if res=='':\n",
    "        return run_code\n",
    "    else:\n",
    "        return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "parser = JsonOutputParser()\n",
    "\n",
    "def get_response_t(messages):\n",
    "    response = Generation.call(\n",
    "        model='qwen-max',\n",
    "        messages=messages,\n",
    "        tools=[convert_to_openai_tool(visrunner)],\n",
    "        result_format='message', # 将输出设置为message形式\n",
    "    )\n",
    "    return response\n",
    "\n",
    "def prompt_vis(content):\n",
    "    system_prompt_t=f\"\"\"已知代码中数据信息如下：\n",
    "{data_0_info}\n",
    "此数据已经赋值给全局变量res_data，在已知res_data的基础上基于pyecharts编写代码然后务必调用工具visrunner完成任务,注意图形的变量名必须是res_vis\n",
    "\"\"\"\n",
    "    prompt=[{\"role\":\"system\",\"content\":system_prompt_t},\\\n",
    "            {\"role\":\"user\",\"content\":\"请基于此数据做柱图\"},\\\n",
    "            {'role': 'assistant', 'content': '', 'tool_calls': [{'function': {'name': 'visrunner', 'arguments': '{\"code\": \"from pyecharts import options as opts\\nfrom pyecharts.charts import Bar\\nres_vis = (Bar().add_xaxis(res_data.index.to_list()).add_yaxis(\"商家A\", res_data.to_list(), stack=\"stack1\").set_series_opts(label_opts=opts.LabelOpts(is_show=False)).set_global_opts(title_opts=opts.TitleOpts(title=\"Bar-堆叠数据（部分）\")))\\nres_vis.render()\"}'}}]}]\n",
    "    prompt.append({\"role\":\"user\",\"content\":content})\n",
    "    return prompt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "p_y=prompt_vis(\"请将数据可视化\")\n",
    "res=get_response_t(p_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "code=res.output.choices[0].message['tool_calls'][0]['function']['arguments'].split('{\"code\": \"')[1].split('\"\\n}')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_vis=visrunner.invoke(code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'F:\\\\langchain\\\\15\\\\render.html'"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_vis.dict()['locals']['res_vis'].render()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyecharts.charts.basic_charts.bar.Bar at 0x24ca652fdf0>"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_vis.dict()['locals']['res_vis']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "st_pyecharts(result_vis.dict()['locals']['res_vis'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GenerationResponse(status_code=<HTTPStatus.OK: 200>, request_id='1fc39d6f-59fb-986a-bda9-aa788ed7f61c', code='', message='', output=GenerationOutput(text=None, choices=[Choice(finish_reason='tool_calls', message=Message({'role': 'assistant', 'content': '', 'tool_calls': [{'function': {'name': 'visrunner', 'arguments': '{\"code\": \"from pyecharts import options as opts\\nfrom pyecharts.charts import Bar\\nres_vis = (\\n    Bar()\\n    .add_xaxis(res_data.index.to_list())\\n    .add_yaxis(\"Count\", res_data.tolist(), category_gap=\"60%\")\\n    .set_global_opts(title_opts=opts.TitleOpts(title=\"Counts Bar Chart\"))\\n)\\nres_vis.render_notebook()\"}'}, 'id': '', 'type': 'function'}]}))], finish_reason=None), usage=GenerationUsage(input_tokens=415, output_tokens=95))"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### classwork2\n",
    "\n",
    "1, 利用上面的函数，完成可视化问答\n",
    "\n",
    "2，在streamlit上实现数据统计+可视化问答应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
